package cn.craccd.sqlHelper.utils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import cn.hutool.core.util.StrUtil;

@Component
public class SqlUtils {
	Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Value("${spring.database.type}")
	String database;
	@Value("${spring.database.package}")
	String packageName;
	@Value("${spring.database.print:false}")
	Boolean print;
	@Autowired
	JdbcTemplate jdbcTemplate;

	String separator = System.getProperty("line.separator");

	
	public String formatSql(String sql) {
		if (StrUtil.isEmpty(sql)) {
			return "";
		}

		if (!database.equalsIgnoreCase("mysql")) {
			sql = sql.replace("`", "\"");
		}

		String tab = "\t";
		sql = sql.replace("FROM", separator + "FROM")//
				.replace("WHERE", separator + "WHERE")//
				.replace("ORDER", separator + "ORDER")//
				.replace("LEFT", separator + "LEFT")//
				.replace("LIMIT", separator + "LIMIT")//
				.replace("AND", separator + tab + "AND")//
				.replace("SET", separator + "SET")//
				.replace("VALUES", separator + "VALUES");//
		return sql;
	}

	public void checkOrCreateTable(Class<?> clazz) {
		String sql = "CREATE TABLE IF NOT EXISTS `" + StrUtil.toUnderlineCase(clazz.getSimpleName()) + "` (id BIGINT NOT NULL PRIMARY KEY)";
		logQuery(formatSql(sql));
		jdbcTemplate.execute(formatSql(sql));

	}

	public void logQuery(String sql) {
		logQuery(sql, null);
	}

	public void logQuery(String sql, Object[] params) {
		if (print) {
			try {
				if (params != null) {
					for (Object object : params) {

						if (object instanceof String) {
							object = object.toString().replace("$", "RDS_CHAR_DOLLAR");
							sql = sql.replaceFirst("\\?", "'" + object + "'").replace("RDS_CHAR_DOLLAR", "$");
						} else {
							sql = sql.replaceFirst("\\?", String.valueOf(object));
						}

					}
				}
				logger.info(separator + sql);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	


	public void checkOrCreateIndex(Class<?> clazz, String name, boolean unique, List<Map<String, Object>> indexs) {
		checkOrCreateIndex(clazz, new String[] { name }, unique, indexs);
	}

	public void checkOrCreateIndex(Class<?> clazz, String[] colums, boolean unique, List<Map<String, Object>> indexs) {
		List<String> columList = new ArrayList<String>();
		for (String colum : colums) {
			columList.add(StrUtil.toUnderlineCase(colum));
		}
		String name = StrUtil.join("&", columList) + "@" + StrUtil.toUnderlineCase(clazz.getSimpleName());

		Boolean hasIndex = false;
		for (Map<String, Object> map : indexs) {
			if (StrUtil.toUnderlineCase(name).equalsIgnoreCase((String) map.get("name")) || StrUtil.toUnderlineCase(name).equalsIgnoreCase((String) map.get("Key_name"))) {
				hasIndex = true;
			}
		}

		if (!hasIndex) {
			String type = unique ? "UNIQUE INDEX" : "INDEX";
			String length = "";
			if (database.equals("mysql")) {
				length = "(128)";
			}

			columList = new ArrayList<String>();
			for (String colum : colums) {
				columList.add(StrUtil.toUnderlineCase("`" + colum + "`" + length));
			}

			String sql = "CREATE " + type + "  `" + StrUtil.toUnderlineCase(name) + "` ON `" + StrUtil.toUnderlineCase(clazz.getSimpleName()) + "`(" + StrUtil.join(",", columList) + ")";
			logQuery(formatSql(sql));
			jdbcTemplate.execute(formatSql(sql));
		}

	}

	public void checkOrCreateColumn(Class<?> clazz, String name, List<Map<String, Object>> columns) {
		Boolean hasColumn = false;
		for (Map<String, Object> map : columns) {
			if (StrUtil.toUnderlineCase(name).equalsIgnoreCase((String) map.get("name")) || StrUtil.toUnderlineCase(name).equalsIgnoreCase((String) map.get("Field"))) {
				hasColumn = true;
			}
		}

		if (!hasColumn) {
			String sql = "ALTER TABLE `" + StrUtil.toUnderlineCase(clazz.getSimpleName()) + "` ADD COLUMN `" + StrUtil.toUnderlineCase(name) + "` TEXT";
			logQuery(formatSql(sql));
			jdbcTemplate.execute(formatSql(sql));
		}

	}


	public void updateDefaultValue(Class<?> clazz, String column, String value) {
		String sql = "SELECT COUNT(*) FROM " + StrUtil.toUnderlineCase(clazz.getSimpleName()) + " WHERE `" + StrUtil.toUnderlineCase(column) + "` IS NULL";
		logQuery(formatSql(sql));
		Long count = jdbcTemplate.queryForObject(formatSql(sql), Long.class);
		if (count > 0) {
			sql = "UPDATE " + StrUtil.toUnderlineCase(clazz.getSimpleName()) + " SET `" + StrUtil.toUnderlineCase(column) + "` = ? WHERE `" + StrUtil.toUnderlineCase(column) + "` IS NULL";
			logQuery(formatSql(sql));
			jdbcTemplate.update(formatSql(sql), value);
		}

	}

}
